import xlrd
import pandas as pd
import json
import openpyxl

jsondata = {
    "year":"all",
    "value":0,
    "data_sexchart": [
      {
        "name": '男',
        "value": 0
      },
      {
       "name": '女',
        "value": 0
      }
    ],
     "data_pie": [
      {
        "name": "国内深造",
        "value": 0
      },
      {
        "name": "国外深造",
        "value": 0
      },
      {
        "name": "三资企业",
        "value": 0
      },
      {
        "name": "中初教育单位",
        "value": 0
      },
      {
        "name": "其他",
        "value": 0
      },
      {
        "name": "其他事业单位",
        "value": 0
      },
      {
        "name": "医疗卫生单位",
        "value": 0
      },
      {
        "name": "国有企业",
        "value": 0
      },
      {
        "name": "机关",
        "value": 0
      },
      {
        "name": "民营企业",
        "value": 0
      },
      {
        "name": "科研设计单位",
        "value": 0
      },
      {
        "name": "部队",
        "value": 0
      }
    ],
     "data_map":[
         
         ],
    "data_wordCloud": [
        
        ],
    "data_sunburst": [
         {
        "name": "全部",
        "value": 0,
        "itemStyle": {
          "borderColor": "#00f2f1",
          "borderWidth": 0
        },
        "children": [
            {
           "name":"就业",
           "value":0,
           "data_sexchart": [
              {
                "name": "男",
                "value": 0
              },
              {
                "name": "女",
                "value": 0
              }
            ],
           "data_pie": [
              ],
           "data_map": [
               ],
           "data_wordCloud":[
               ],
           "children": [
              
               ],
          },
          {
           "name":"深造",
           "value":0,
           "data_sexchart":[],
           "data_pie":[],
           "data_map":[],
           "data_wordCloud":[],
              },
        ],
      }
    ],
 }
wb = openpyxl.load_workbook(r'all.xlsx')
ws = wb.active
ming = 0
guo = 0
dang = 0
bu = 0
qi = 0
key = 0
rowi = 1
columni = 4
x = 0
ss = 0
while(rowi < 2100):
    key = 0;
    value2 = ws.cell(row = rowi,column = 2).value
    value3 = ws.cell(row = rowi,column = 4).value
    value1 = ws.cell(row = rowi,column = 1).value
    value4 = ws.cell(row = rowi,column = 5).value
    value6 = ws.cell(row = rowi,column = 3).value
    value7 = ws.cell(row = rowi,column = 8).value
    c = str(value4)[0:2]
    flag = 0
    flag1 = 0;
    f = 0
    s = 0
 
    if(value7 != "签约单位领域" and str(value7) != "None"):
       
        for item in jsondata["data_sunburst"]:
            item["value"] += 1
            for item2 in item["children"]:
               if(item2["name"] == "就业"):
                 item2["value"] += 1
                 for item3 in item2["data_sexchart"]:
                     if(item3["name"] == value1):
                         item3["value"] += 1
                         s = 1
                 if(s == 0 and value1 != "性别"):
                    item2["data_sexchart"].append({"name":value1,"value":1})
                 s = 0
                 for item3 in item2["data_pie"]:
                       if(item3["name"] == value3):
                           item3["value"] += 1
                           s = 1
                 if(s == 0 and value3 !=  "签约单位性质"):
                     item2["data_pie"].append({"name":value3,"value":1})
                 s = 0
                 for item3 in item2["data_map"]:
                            if(item3["name"] == c ):
                              item3["value"] += 1
                              s = 1
                 if(s == 0 and value4 != "签约单位省市"):
                            item2["data_map"].append({"name":c,"value":1})
                 s = 0
                 for item3 in item2["data_wordCloud"]:
                            if(item3["name"] == value6 ):
                              item3["value"] += 1
                              s = 1
                 if(s == 0 and value6 != "签约单位"):
                            item2["data_wordCloud"].append({"name":value6,"value":1})
                 s = 0
                 for item3 in item2["children"]:
                    if(item3["name"] == value7):
                        item3["value"] += 1
                        f = 1
                        for item4 in item3["data_sexchart"]:
                           if(item4["name"] == value1 ):
                             item4["value"] += 1
                             s = 1
                        if(s == 0 and value1 != "性别"):
                           item3["data_sexchart"].append({"name":value1,"value":1})
                        s = 0
                        for item4 in item3["data_pie"]:
                            if(item4["name"] == value3 ):
                             item4["value"] += 1
                             s = 1
                        if(s == 0 and value3 != "签约单位性质"):
                            item3["data_pie"].append({"name":value3,"value":1})
                        s = 0
                        for item4 in item3["data_map"]:
                            if(item4["name"] == c ):
                              item4["value"] += 1
                              s = 1
                        if(s == 0 and value4 != "签约单位省市"):
                            item3["data_map"].append({"name":c,"value":1})
                        s = 0
                        for item4 in item3["data_wordCloud"]:
                            if(item4["name"] == value6 ):
                              item4["value"] += 1
                              s = 1
                        if(s == 0 and value6 != "签约单位"):
                            item3["data_wordCloud"].append({"name":value6,"value":1})
                    
                 if(f == 0 and value1 != "性别" and str(value7) != "None" ):
                    item2["children"].append({"name":value7,"value":1,"data_sexchart":[{"name":value1,"value":1}],"data_pie":[{"name":value3,"value":1}],
                                              "data_map":[{"name":c,"value":1}], "data_wordCloud":[{"name":value6,"value":1}],})
                 
    s = 0
    if(value2 == "出国、出境" or value2 ==  "研究生"):
        for item in jsondata["data_sunburst"]:
            item["value"] += 1
            for item2 in item["children"]:
                if(item2["name"] == "深造"):
                   item2["value"] += 1
                   for item4 in item2["data_sexchart"]:
                           if(item4["name"] == value1 ):
                             item4["value"] += 1
                             s = 1

                   if(s == 0 and value1 != "性别"):
                           item2["data_sexchart"].append({"name":value1,"value":1})
                   s = 0
                   for item4 in item2["data_pie"]:
                            if(item4["name"] == "国内" and value2 ==  "研究生"):
                             item4["value"] += 1
                             s = 1
                            if(item4["name"] == "国外" and value2 ==  "出国、出境"):
                             item4["value"] += 1
                             s = 1
                   if(s == 0 and value3 != "签约单位性质"):
                            if(value2 == "出国、出境"):
                              item2["data_pie"].append({"name":"国外","value":1})
                            if(value2 == "研究生"):
                              item2["data_pie"].append({"name":"国内","value":1})
                        
                   s = 0
                   for item4 in item2["data_map"]:
                            if(item4["name"] == c ):
                              item4["value"] += 1
                              s = 1
                   if(s == 0 and value4 != "签约单位省市" and value2 != "出国、出境"):
                            item2["data_map"].append({"name":c,"value":1})
                   s = 0
                   for item4 in item2["data_wordCloud"]:
                            if(item4["name"] == value6 ):
                              item4["value"] += 1
                              s = 1
                   if(s == 0 and value6 != "签约单位"):
                            item2["data_wordCloud"].append({"name":value6,"value":1})

                    
    f = 0


    #if(value2 == "签就业协议形式就业" or value2 == "自由职业" or value2 == "其他录用形式就业" or value2 == "签劳动合同形式就业"):
    #    for item in jsondata["data_sunburst"]:
    #        for item2 in item["children"]:
    #            if(item2["name"] == "就业"):
    #              item2["value"] += 1
    #              for item3 in item2["data_sexchart"]:
    #                if(value1 == item3["name"]):
    #                    item3["value"] += 1;
    #              for item3 in item2["data_map"]:
    #                if(c == item3["name"]):
    #                    item3["value"] += 1
    #                    f = 1
    #              if(f == 0 and c != "No" and c != "签约"):
    #               item2["data_map"].append({"name":c,"value":1})
    #              f = 0
    #              for item3 in item2["data_wordCloud"]:
    #                if(value6 == item3["name"]):
    #                    item3["value"] += 1;
    #                    f = 1
    #              if(f == 0):
    #                item2["data_wordCloud"].append({"name":value6,"value":1})
    
            
    if(value2 == "出国、出境" or value2 ==  "研究生" or value7 != "签约单位领域" and str(value7) != "None"):
         jsondata["value"] += 1
         for item in jsondata["data_sexchart"]:
             if(item["name"] == value1):
                 item["value"] += 1
         for item in jsondata["data_pie"]:
             if(item["name"] == "国外深造" and value2 == "出国、出境"):
                item["value"] += 1
             if(value2 == "研究生" and item["name"] == "国内深造"):
                  item["value"] += 1
         for item in jsondata["data_pie"]:
             if(item["name"] == value3):
                  item["value"] += 1    
                             
                             
         for item in jsondata["data_map"]:
             if(item["name"] == c):
                 item["value"] += 1
                 flag = 1
         if(flag == 0 and c != "No" and c != "签约"):
             jsondata["data_map"].append({"name":c,"value":1})

         for item in jsondata["data_wordCloud"]:
             if(item["name"] == value6):
                 item["value"] += 1
                 flag1 = 1
         if(flag1 == 0):
             jsondata["data_wordCloud"].append({"name":value6,"value":1})


    #if(value1 == "男"):
    #    for item in jsondata["data_sexchart"]:
    #        if(item["name"] == "男"):
    #             item["value"] += 1
    #if(value1 == "女"):
    #    for item in jsondata["data_sexchart"]:
    #        if(item["name"] == "女"):
    #             item["value"] += 1
            
    #if(value2 == "出国、出境"):
    #    for item in jsondata["data_pie"]:
    #      if(item["name"] == "国外深造"):
    #        item["value"] += 1
    #if(value2 == "研究生"):
    #    for item in jsondata["data_pie"]:
    #      if(item["name"] == "国内深造"):
    #        item["value"] += 1
    #key = 0
    
    #        key = 1;
    #if(key == 0 and (value2 == "签就业协议形式就业" or value2 == "自由职业" or value2 == "其他录用形式就业" or value2 == "签劳动合同形式就业")):
    #   for item in jsondata["data_pie"]:
    #     if(item["name"] == "其他"  ):
    #       item["value"] += 1 
    #for item in jsondata["data_map"]:
    #    if(item["name"] == c):
    #        item["value"] += 1
    #        flag = 1
    #if(flag == 0 and c != "No" and c != "签约"):
    #    jsondata["data_map"].append({"name":c,"value":1})
    #for item in jsondata["data_wordCloud"]:
    #    if(item["name"] == value6):
    #        item["value"] += 1
    #        flag1 = 1
    #if(flag1 == 0):
    #    jsondata["data_wordCloud"].append({"name":value6,"value":1})
   
    rowi += 1
for item in jsondata["data_wordCloud"]:
    if(item["value"] < 3):
         del item["name"]
         del item["value"]
for item in jsondata["data_sunburst"]:
    for item2 in item["children"]:
        if(item2["name"] == "就业"):
         for item3 in item2["data_wordCloud"]:
            if(item3["value"] < 3):
                del item3["name"]
                del item3["value"]   
print(ss)
with open(r"‪json.json", 'w') as f:
   file=json.dump(jsondata,f,ensure_ascii=False)
